package org.chenwei.week10;


import java.sql.*;
import java.util.ArrayList;
import java.util.List;


/**
 *
 * @author chenwei
 */
public class DBManager
{
    public DBManager() throws ClassNotFoundException {
        // load the sqlite-JDBC driver using the current class loader
        Class.forName("org.sqlite.JDBC");
        
        try {
            // create a database connection
            connection = DriverManager.getConnection("jdbc:sqlite:" + System.getProperty("user.home") + "/students.db");
            statement = connection.createStatement();
            statement.setQueryTimeout(30);  // set timeout to 30sec.
            
            statement.executeUpdate("create table if not exists " + TABLE + " ("
                    + "id integer primary key, name string, "
                    + "nationality string, graduate string, "
                    + "department string, email string, "
                    + "phone text)");
        }
        catch (SQLException e) {
            // if the error message is "out of memory",
            // it probably means no database file is found.
            System.err.println(e.getMessage());
        }
        finally {
            dbExit();
        }
    }
    
    Connection connection = null;
    Statement statement = null;
    private String TABLE = "students";
    
    //DONE: register a student
    public void register(Student student) {
        try {
            Student s = search(student.getId());
            String sql = null;
            if (s == null) {
                sql = "insert into " + TABLE + " values("
                        + student.getId() + ", "
                        + "'" + student.getName() + "', "
                        + "'" + student.getNationality() + "', "
                        + "'" + student.getGraduate() + "', "
                        + "'" + student.getDepartment() + "', "
                        + "'" + student.getEmail() + "', "
                        + "'" + student.getPhone() + "'"
                        + ")";
                statement.executeUpdate(sql);
            }
            else {
                sql = "update " + TABLE + " set "
                        + "name = '" + student.getName() + "', "
                        + "nationality = '" + student.getNationality() + "', "
                        + "graduate = '" + student.getGraduate() + "', "
                        + "department = '" + student.getDepartment() + "', "
                        + "email = '" + student.getEmail() + "', "
                        + "phone = '" + student.getPhone() + "' "
                        + "where id = " + student.getId();
                statement.executeUpdate(sql);
            }
        }
        catch (SQLException e) {
            // if the error message is "out of memory",
            // it probably means no database file is found.
            System.err.println(e.getMessage());
        }
    }
    
    //DONE: search student information
    public Student search(int id) {
        Student student = null;
        try {
            String sql = "select * from " + TABLE + " where id = " + id;
            ResultSet rs = statement.executeQuery(sql);
            
            if (rs.next()) {
                student = new Student();
                student.setId(id);
                student.setName(rs.getString("name"));
                student.setNationality(rs.getString("nationality"));
                student.setGraduate(rs.getString("graduate"));
                student.setDepartment(rs.getString("department"));
                student.setEmail(rs.getString("email"));
                student.setPhone(rs.getString("phone"));
            }
        }
        catch (SQLException e) {
            // if the error message is "out of memory",
            // it probably means no database file is found.
            System.err.println(e.getMessage());
        }
        
        return student;
    }
    
    //DONE: search all student information
    public List<Student> searchAll() {
        Student student = null;
        ArrayList<Student> list = new ArrayList<Student>();
        
        try {
            String sql = "select * from " + TABLE;
            ResultSet rs = statement.executeQuery(sql);
            
            while (rs.next()) {
                student = new Student();
                student.setId(rs.getInt("id"));
                student.setName(rs.getString("name"));
                student.setNationality(rs.getString("nationality"));
                student.setGraduate(rs.getString("graduate"));
                student.setDepartment(rs.getString("department"));
                student.setEmail(rs.getString("email"));
                student.setPhone(rs.getString("phone"));
                
                list.add(student);
            }
        }
        catch (SQLException e) {
            // if the error message is "out of memory",
            // it probably means no database file is found.
            System.err.println(e.getMessage());
        }
        
        return list;
    }
    
    private void dbExit() {
        Runtime.getRuntime().addShutdownHook(new Thread() {
            @Override
            public void run() {
                System.out.println("Exit...");
                try {
                    if (connection != null) {
                        connection.close();
                    }
                }
                catch (SQLException e) {
                    // connection close failed
                    System.err.println(e);
                }
            }
        });
    }
}
